package studysystem.demo.mapper;

import org.apache.ibatis.annotations.*;
import studysystem.demo.bean.*;

import java.util.List;

@Mapper
public interface LearnerMapper {

    //获取用户角色
    @Select("select role from learner where phone = #{phone,jdbcType=VARCHAR}")
    Role findRole(String phone);

    //获取用户权限---UNDO
    @Select("select * from permission where role = #{role,jdbcType=VARCHAR}")
    List<Permission> findPermission(String role);

    /**新增用户角色
    @Insert("insert into role(username, role) values (#{username,jdbcType=VARCHAR}, #{role,jdbcType=VARCHAR})")
    void saveRole(Role roleEntity);

    //删除用户角色
    @Delete("delete from role where username = #{username,jdbcType=VARCHAR}")
    void deleteRole(String username);
     **/
    //修改学习者密码
    @Update("update learner set password=#{password,jdbcType=VARCHAR} where phone=#{phone,jdbcType=VARCHAR}")
    void updatePsw(Learner learnerEntity);

    //新建学习者
    @Insert("insert into learner (phone, password, name, sex, userid, role) values (#{phone,jdbcType=VARCHAR}," +
            " #{password,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR}, #{userid,jdbcType=VARCHAR}, '学习者')")
    void saveLearner(Learner learnerEntity);

    //更新学习者
    @Update("update learner set name = #{name,jdbcType=VARCHAR}, sex = #{sex,jdbcType=VARCHAR}, userid = #{userid,jdbcType=VARCHAR} where phone = #{phone,jdbcType=VARCHAR}")
    void updateLearner(Learner learnerEntity);

    //删除学习者
    @Delete("delete from learner where phone = #{phone,jdbcType=VARCHAR}")
    void deleteLearner(String phone);

    //查询学习者信息
    @Select("select phone, password, name, sex, userid, role from learner where phone = #{phone,jdbcType=VARCHAR}")
    Learner findLearnerInfo(String username);

    //查询学习者密码
    @Select("select password from learner where phone = #{phone,jdbcType=VARCHAR}")
    Learner findLearnerPsw(String phone);

    //查询是否存在该学习方向
    @Select("select name from course_class where name = #{class_dir,jdbcType=VARCHAR}")
    String checkCoursedir(String class_dir);

    //查询学习者学习方向
    @Select("select course_dir from learner where phone = #{phone,jdbcType=VARCHAR}")
    String findCoursedir(String phone);

    //选择、修改学习方向
    @Update("update learner set course_dir = #{course_dir,jdbcType=VARCHAR} where phone = #{phone,jdbcType=VARCHAR}")
    void updateCoursedir(@Param("phone")String phone, @Param("course_dir")String course_dir);

    //查询所有学习者条数
    @Select("select count(*) from learner where role = '学习者'")
    int findLearnerCountAll();

    //查询所有学习者
    @Select("select phone, name, sex, userid, role from learner where role = '学习者' order by name desc")
    List<Learner> findLearnerAll();

    //查询学习者条数 (用户名  模糊查询)
    @Select("select count(*) from learner where phone like CONCAT(CONCAT('%', #{phone}), '%')")
    int findLearnerCountByPhone(String phone);

    //查询学习者(用户名  模糊查询)
    @Select("select phone, name, sex, userid, role from learner where phone like CONCAT(CONCAT('%', #{phone}), '%') and role = '学习者' order by name desc")
    List<Learner> findLearnerByPhone(String phone);

    //查询学习者条数 (姓名  模糊查询)
    @Select("select count(*) from learner where name like CONCAT(CONCAT('%', #{name}), '%') and role = '学习者'")
    int findLearnerCountByName(String name);

    //查询学习者用户(姓名  模糊查询)
    @Select("select phone, name, sex, userid, role  from learner where name like CONCAT(CONCAT('%', #{name}), '%') and role = '学习者' order by name desc")
    List<Learner> findLearnerByName(String name);
    @Select("select phone   from learner where name = #{name,jdbcType=VARCHAR}")
    String findLearnerPhoneByName(String name);
    //检验是否存在学习者
    @Select("select name from learner where name like CONCAT(CONCAT('%', #{name}), '%') ")
    String findLearnerByName2(String name);


}
